package com.gugee.tiktok.common.model.dal.blogger;

import com.gugee.tiktok.common.model.mysql.Blogger;
import com.gugee.tiktok.common.model.mysql.BloggerMain;
import com.xiguaji.boot.toolkit.DateTimeUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Lazy;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.util.StringUtils;

import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;
import java.util.stream.Collectors;

public class BloggerMainExtRepositoryImpl implements BloggerMainExtRepository {
    @Autowired
    JdbcTemplate jdbcTemplate;

    @Autowired
    @Lazy
    BloggerMainRepository bloggerMainRepository;

    @Override
    public void batchInsert(List<Blogger> bloggerList, String who) {
        long seconds = DateTimeUtils.currentTimeSecond();

        jdbcTemplate.batchUpdate(
                "INSERT INTO `blogger_main` " +
                        "(`id`, `o_uid`, `o_avatar300`, `o_avatar_medium`, `o_follower_count`, `o_district`, `o_signature`, `o_mplatform_followers_count`, `o_cover_url`, `o_is_star`, `o_avg_views`, " +
                        "`o_avg_likes`, `o_avg_reviews`, `o_avg_share`, `o_following_count`, `o_total_favorited`, `o_dongtai_count`, `o_avatar168`, `o_gender`, `o_share_info`, `o_avatar_larger`," +
                        "`o_birthday`, `o_country`, `o_city`, `o_is_activity_user`, `o_avatar_thumb`, `o_province`, `o_aweme_count`, `o_location`, `o_sec_uid`, `o_nickname`, `o_room_id`, `o_region`," +
                        "`o_video_icon`, `o_unique_id`, `o_constellation`, `enterprise_verify_reason`, `with_fusion_shop_entry`, `blogger_type`, `blogger_type_audit_status`, `score`,`create_time`, `update_time`, `create_by`, `update_by`," +
                        "`bio_url`,  `custom_verify`, `youtube_channel_id`, `twitter_id`, `ins_id`, `bio_email`)"+
                        " VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
                , new BatchPreparedStatementSetter() {
                    @Override
                    public void setValues(PreparedStatement preparedStatement, int i) throws SQLException {
                        Blogger blogger = bloggerList.get(i);
                        preparedStatement.setLong(1, 0);
                        preparedStatement.setLong(2, blogger.getOUid());
                        preparedStatement.setString(3, blogger.getOAvatar300());
                        preparedStatement.setString(4, blogger.getOAvatarMedium());
                        preparedStatement.setInt(5, blogger.getOFollowerCount());
                        preparedStatement.setString(6, blogger.getODistrict());
                        preparedStatement.setString(7, blogger.getOSignature());
                        preparedStatement.setInt(8, blogger.getOMplatformFollowersCount());
                        preparedStatement.setString(9, blogger.getOCoverUrl());
                        preparedStatement.setBoolean(10, blogger.isOIsStar());
                        preparedStatement.setLong(11, blogger.getOAvgViews());
                        preparedStatement.setLong(12, blogger.getOAvgLikes());
                        preparedStatement.setLong(13, blogger.getOAvgReviews());
                        preparedStatement.setLong(14, blogger.getOAvgShare());
                        preparedStatement.setInt(15, blogger.getOFollowingCount());
                        preparedStatement.setLong(16, blogger.getOTotalFavorited());
                        preparedStatement.setInt(17, blogger.getODongtaiCount());
                        preparedStatement.setString(18, blogger.getOAvatar168());
                        preparedStatement.setInt(19, blogger.getOGender());
                        preparedStatement.setString(20, blogger.getOShareInfo());
                        preparedStatement.setString(21, blogger.getOAvatarLarger());
                        preparedStatement.setString(22, blogger.getOBirthday());
                        preparedStatement.setString(23, blogger.getOCountry());
                        preparedStatement.setString(24, blogger.getOCity());
                        preparedStatement.setBoolean(25, blogger.isOIsActivityUser());
                        preparedStatement.setString(26, blogger.getOAvatarThumb());
                        preparedStatement.setString(27, blogger.getOProvince());
                        preparedStatement.setInt(28, blogger.getOAwemeCount());
                        preparedStatement.setString(29, blogger.getOLocation());
                        preparedStatement.setString(30, blogger.getOSecUid());
                        preparedStatement.setString(31, blogger.getONickname());
                        preparedStatement.setLong(32, blogger.getORoomId());
                        preparedStatement.setString(33, blogger.getORegion());
                        preparedStatement.setString(34, blogger.getOVideoIcon());
                        preparedStatement.setString(35, blogger.getOUniqueId());
                        preparedStatement.setLong(36, blogger.getOConstellation());
                        preparedStatement.setString(37, blogger.getEnterpriseVerifyReason());
                        preparedStatement.setBoolean(38, blogger.getWithFusionShopEntry());
                        preparedStatement.setString(39, "");
                        preparedStatement.setInt(40, 0);
                        preparedStatement.setInt(41, 0);
                        preparedStatement.setLong(42, seconds);
                        preparedStatement.setLong(43, seconds);
                        preparedStatement.setString(44, who);
                        preparedStatement.setString(45, who);

                        preparedStatement.setString(46, blogger.getBioUrl());
                        preparedStatement.setString(47, blogger.getCustomVerify());
                        preparedStatement.setString(48, blogger.getYoutubeChannelId());
                        preparedStatement.setString(49, blogger.getTwitterId());
                        preparedStatement.setString(50, blogger.getInsId());
                        preparedStatement.setString(51, blogger.getBioEmail());
                    }

                    @Override
                    public int getBatchSize() {
                        return bloggerList.size();
                    }
                });
    }
    @Override
    public void batchUpdate(List<BloggerMain> bloggerList){
        long seconds = DateTimeUtils.currentTimeSecond();
        Date now=new Date(System.currentTimeMillis());
        jdbcTemplate.batchUpdate(
                "UPDATE `tiktok`.`blogger_main` SET  " +
                        "`last_collect_time` = ?, `o_uid` = ?, `o_avatar300` = ?, `o_avatar_medium` = ?, `o_follower_count` = ?, `o_district` = ?, `o_signature` = ?, `o_mplatform_followers_count` = ?, `o_cover_url` = ?, `o_is_star` = ?, `o_avg_views` = ?, " +
                        "`o_avg_likes` = ?, `o_avg_reviews` = ?, `o_avg_share` = ?, `o_following_count` = ?, `o_total_favorited` = ?, `o_dongtai_count` = ?, `o_avatar168` = ?, `o_gender` = ?, `o_share_info` = ?, `o_avatar_larger` = ?," +
                        "`o_birthday` = ?, `o_country` = ?, `o_city` = ?, `o_is_activity_user` = ?, `o_avatar_thumb` = ?, `o_province` = ?, `o_aweme_count` = ?, `o_location` = ?, `o_sec_uid` = ?, `o_nickname` = ?, `o_room_id` = ?, `o_region` = ?," +
                        "`o_video_icon` = ?, `o_unique_id` = ?, `o_constellation` = ?, `enterprise_verify_reason` = ?, `with_fusion_shop_entry` = ?, `score` = ?, `update_time` = ?, `update_by` = ?,`last_collect_time` = ?, "+
                        "`bio_url` = ?, `custom_verify` = ?, `youtube_channel_id` = ?, `twitter_id` = ?,`ins_id` = ? , `bio_email`= ? "+
                        " WHERE `o_uid` = ? "

                , new BatchPreparedStatementSetter() {
                    @Override
                    public void setValues(PreparedStatement preparedStatement, int i) throws SQLException {
                        BloggerMain blogger = bloggerList.get(i);


                        preparedStatement.setDate(1, now);
                        preparedStatement.setLong(2, blogger.getOUid());
                        preparedStatement.setString(3, blogger.getOAvatar300());
                        preparedStatement.setString(4, blogger.getOAvatarMedium());
                        preparedStatement.setInt(5, blogger.getOFollowerCount());
                        preparedStatement.setString(6, blogger.getODistrict());
                        preparedStatement.setString(7, blogger.getOSignature());
                        preparedStatement.setInt(8, blogger.getOMplatformFollowersCount());
                        preparedStatement.setString(9, blogger.getOCoverUrl());
                        preparedStatement.setBoolean(10, blogger.isOIsStar());
                        preparedStatement.setLong(11, blogger.getOAvgViews());
                        preparedStatement.setLong(12, blogger.getOAvgLikes());
                        preparedStatement.setLong(13, blogger.getOAvgReviews());
                        preparedStatement.setLong(14, blogger.getOAvgShare());
                        preparedStatement.setInt(15, blogger.getOFollowingCount());
                        preparedStatement.setLong(16, blogger.getOTotalFavorited());
                        preparedStatement.setInt(17, blogger.getODongtaiCount());
                        preparedStatement.setString(18, blogger.getOAvatar168());
                        preparedStatement.setInt(19, blogger.getOGender());
                        preparedStatement.setString(20, blogger.getOShareInfo());
                        preparedStatement.setString(21, blogger.getOAvatarLarger());
                        preparedStatement.setString(22, blogger.getOBirthday());
                        preparedStatement.setString(23, blogger.getOCountry());
                        preparedStatement.setString(24, blogger.getOCity());
                        preparedStatement.setBoolean(25, blogger.isOIsActivityUser());
                        preparedStatement.setString(26, blogger.getOAvatarThumb());
                        preparedStatement.setString(27, blogger.getOProvince());
                        preparedStatement.setInt(28, blogger.getOAwemeCount());
                        preparedStatement.setString(29, blogger.getOLocation());
                        preparedStatement.setString(30, blogger.getOSecUid());
                        preparedStatement.setString(31, blogger.getONickname());
                        preparedStatement.setLong(32, blogger.getORoomId());
                        preparedStatement.setString(33, blogger.getORegion());
                        preparedStatement.setString(34, blogger.getOVideoIcon());
                        preparedStatement.setString(35, blogger.getOUniqueId());
                        preparedStatement.setLong(36, blogger.getOConstellation());
                        preparedStatement.setString(37, blogger.getEnterpriseVerifyReason());
                        preparedStatement.setBoolean(38, blogger.getWithFusionShopEntry());
                        preparedStatement.setDouble(39, blogger.getScore());
                        preparedStatement.setLong(40, seconds);
                        preparedStatement.setString(41, "BatchUpdate");
                        preparedStatement.setDate(42, now);
                        preparedStatement.setLong(43, blogger.getOUid());


                        preparedStatement.setString(44, blogger.getBioUrl());
                        preparedStatement.setString(45, blogger.getCustomVerify());
                        preparedStatement.setString(46, blogger.getYoutubeChannelId());
                        preparedStatement.setString(47, blogger.getTwitterId());
                        preparedStatement.setString(48, blogger.getInsId());
                        preparedStatement.setString(49, blogger.getBioEmail());
                    }

                    @Override
                    public int getBatchSize() {
                        return bloggerList.size();
                    }
                });
    }

    @Override
    public List<Long> getNotExistBloggerIds(List<Long> uids) {
        return uids.stream().filter(uid -> !bloggerMainRepository.existsByoUid(uid)).collect(Collectors.toList());
    }


    @Override
    public List<BloggerMain> getBloggersByBloggerTypeSortDesc(String bloggerType,int limit){
        StringBuffer sql = new StringBuffer("select * from blogger_main ");

        if(!StringUtils.isEmpty(bloggerType)){
            sql.append(" where blogger_type = ").append("'").append(bloggerType).append("'");
        }
        sql.append(" order by score desc limit 0,").append(limit);
        RowMapper<BloggerMain> rowMapper = BeanPropertyRowMapper.newInstance(BloggerMain.class);
        return jdbcTemplate.query(sql.toString(), rowMapper);
    }

    public List<BloggerMain> getBloggersByEnterpriseVerify(String bloggerType,int limit){
        StringBuffer sql = new StringBuffer("select * from blogger_main ");
        sql.append("where enterprise_verify_reason = 'Verified account' ");

        if(!StringUtils.isEmpty(bloggerType)){
            sql.append(" and blogger_type = ").append("'").append(bloggerType).append("'");
        }
        sql.append(" order by score desc limit 0,").append(limit);
        RowMapper<BloggerMain> rowMapper = BeanPropertyRowMapper.newInstance(BloggerMain.class);
        return jdbcTemplate.query(sql.toString(), rowMapper);
    }

    public List<BloggerMain> getBloggersByRegion(String region,int limit){
        StringBuffer sql = new StringBuffer("select * from blogger_main ");

        if(!StringUtils.isEmpty(region)){
            sql.append("where o_region = ").append("'").append(region).append("'");
        }
        sql.append(" order by score desc limit 0,").append(limit);
        RowMapper<BloggerMain> rowMapper = BeanPropertyRowMapper.newInstance(BloggerMain.class);
        return jdbcTemplate.query(sql.toString(), rowMapper);
    }

//    public void update(BloggerMain blogger, String operator) {
//
//            bloggerMainRepository.save(blogger);
//
//
//    }

}
